﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using V5_DBUtility;
using V5_Common;

namespace V5_DAL.SqlServer {
    public class DALSQL {

        public DataTable Query(string SQL) {
            return DbHelperSQL.Query(SQL).Tables[0];
        }

        public DataTable Query_By(string what, string from, string where, string orderby, int limit) {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append(" Select ");
            if (limit == 0) {
                sbSQL.AppendFormat(" Top {0} ", 10);
            }
            else {
                sbSQL.AppendFormat(" Top {0} ", limit);
            }
            if (string.IsNullOrEmpty(what)) {
                sbSQL.AppendFormat(" {0} ", "*");
            }
            else {
                sbSQL.AppendFormat(" {0} ", what);
            }
            sbSQL.Append(" From " + from + " ");
            if (!string.IsNullOrEmpty(where)) {
                sbSQL.AppendFormat(" Where {0} ", where);
            }
            if (!string.IsNullOrEmpty(orderby)) {
                sbSQL.AppendFormat(" Order By {0} ", orderby);
            }
            return DbHelperSQL.Query(sbSQL.ToString()).Tables[0];
        }


        public DataTable Query_Pager(string what, string from, string where, string orderby, int limit, int pageIndex, string indexName, ref int RecordCount) {
            if (pageIndex == 0) {
                pageIndex = 1;
            }
            StringBuilder sbSQL = new StringBuilder();
            //计算出总个数
            string sql = "Select count(" + indexName + ") from " + from + " ";
            if (!string.IsNullOrEmpty(where)) {
                sql += " Where " + where;
            }
            RecordCount = int.Parse("0" + DbHelperSQL.GetSingle(sql));
            if (pageIndex == 0 || pageIndex == 1) {
                sbSQL.Append(" Select ");
                if (limit == 0) {
                    limit = 10;
                    sbSQL.AppendFormat(" Top {0} ", 10);
                }
                else {
                    sbSQL.AppendFormat(" Top {0} ", limit);
                }
                if (string.IsNullOrEmpty(what)) {
                    sbSQL.AppendFormat(" {0} ", "*");
                }
                else {
                    sbSQL.AppendFormat(" {0} ", what);
                }
                sbSQL.Append(" From " + from + " ");
                if (!string.IsNullOrEmpty(where)) {
                    sbSQL.AppendFormat(" Where {0} ", where);
                }
                if (!string.IsNullOrEmpty(orderby)) {
                    sbSQL.AppendFormat(" Order By {0} ", orderby);
                }
            }
            else {
                sbSQL.Append(" Select ");
                if (limit == 0) {
                    limit = 10;
                    sbSQL.AppendFormat(" Top {0} ", 10);
                }
                else {
                    sbSQL.AppendFormat(" Top {0} ", limit);
                }
                if (string.IsNullOrEmpty(what)) {
                    sbSQL.AppendFormat(" {0} ", "*");
                }
                else {
                    sbSQL.AppendFormat(" {0} ", what);
                }
                sbSQL.Append(" From " + from + " ");
                int topNum = (pageIndex - 1) * int.Parse("0" + limit);
                string ss = " Where " + indexName + " Not in(Select Top " + topNum + " " + indexName + " From " + from + " ";
                if (!string.IsNullOrEmpty(where)) {
                    ss += string.Format(" Where {0} ", where);
                }
                if (!string.IsNullOrEmpty(orderby)) {
                    ss += string.Format(" Order by {0} ", orderby);
                }
                ss += ")";
                sbSQL.AppendFormat(ss);
                if (!string.IsNullOrEmpty(where)) {
                    sbSQL.AppendFormat(" And {0} ", where);
                }
                if (!string.IsNullOrEmpty(orderby)) {
                    sbSQL.AppendFormat(" Order By {0} ", orderby);
                }
            }
            return DbHelperSQL.Query(sbSQL.ToString()).Tables[0];
        }
    }
}
